Database

In [12]:

    

import sqlite3
#driver is being imported
#psycopg2 for protsgeSQl
#pymysql for mySQL


    

In [13]:

    

conn= sqlite3.connect('example.sqlite3')
#if the give nname of file exits it load s the file else it creates the file


    

In [14]:

    

cur= conn.cursor()
#like pointer


    

In [15]:

    

cur.execute('CREATE TABLE countries(id integer, name text,iso3 text)')
#creates a table with columns id name and iso3


    

    




---------------------------------------------------------------------------
OperationalError                          Traceback (most recent call last)
<ipython-input-15-4823daa2362e> in <module>()
----> 1 cur.execute('CREATE TABLE countries(id integer, name text,iso3 text)')
      2 #creates a table with columns id name and iso3

OperationalError: table countries already exists

In [16]:

    

cur.execute('SELECT * FROM countries')


    

    
Out[16]:





<sqlite3.Cursor at 0x277e779d0a0>

In [10]:

    

cur.fetchall()
#data not entered in our table


    

    
Out[10]:





[]

In [19]:

    

cur.execute('INSERT INTO countries(id,name,iso3)VALUES(1,"Nepal","NEP")')


    

    
Out[19]:





<sqlite3.Cursor at 0x277e779d0a0>

In [20]:

    

cur.execute('SELECT * FROM countries')


    

    
Out[20]:





<sqlite3.Cursor at 0x277e779d0a0>

In [21]:

    

cur.fetchall()


    

    
Out[21]:





[(1, 'Nepal', 'NEP')]

In [22]:

    

sql='''INSERT INTO countries (id,name,iso3) VALUES (?,?,?)'''
cur.executemany(sql,[(2,'India','INA'),
                     (3,'Bhutan','BHU'),
                     (4,'Afghanistan','AFG')])


    

    
Out[22]:





<sqlite3.Cursor at 0x277e779d0a0>

In [23]:

    

cur.execute('SELECT * FROM countries')


    

    
Out[23]:





<sqlite3.Cursor at 0x277e779d0a0>

In [24]:

    

cur.fetchall()


    

    
Out[24]:





[(1, 'Nepal', 'NEP'),
 (2, 'India', 'INA'),
 (3, 'Bhutan', 'BHU'),
 (4, 'Afghanistan', 'AFG')]

In [25]:

    

sql='''INSERT INTO countries (id,name,iso3) VALUES (4,'Pakistan','PAK')'''
cur.execute(sql)


    

    
Out[25]:





<sqlite3.Cursor at 0x277e779d0a0>

In [26]:

    

cur.execute('SELECT * FROM countries')
cur.fetchall()


    

    
Out[26]:





[(1, 'Nepal', 'NEP'),
 (2, 'India', 'INA'),
 (3, 'Bhutan', 'BHU'),
 (4, 'Afghanistan', 'AFG'),
 (4, 'Pakistan', 'PAK')]

In [28]:

    

sql='UPDATE countries SET id=5 WHERE iso3= "PAK"'
cur.execute(sql)


    

    
Out[28]:





<sqlite3.Cursor at 0x277e779d0a0>

In [29]:

    

cur.execute('SELECT * FROM countries')
cur.fetchall()


    

    
Out[29]:





[(1, 'Nepal', 'NEP'),
 (2, 'India', 'INA'),
 (3, 'Bhutan', 'BHU'),
 (4, 'Afghanistan', 'AFG'),
 (5, 'Pakistan', 'PAK')]

In [30]:

    

conn.commit()
#to write in the database


    

In [31]:

    

cur.execute('SELECT * FROM countries WHERE id=4')
cur.fetchall()


    

    
Out[31]:





[(4, 'Afghanistan', 'AFG')]

In [32]:

    

cur.execute('SELECT * FROM countries WHERE id>3')
cur.fetchall()


    

    
Out[32]:





[(4, 'Afghanistan', 'AFG'), (5, 'Pakistan', 'PAK')]

In [34]:

    

cur.execute('SELECT * FROM countries WHERE name LIKE "%an"')
cur.fetchall()


    

    
Out[34]:





[(3, 'Bhutan', 'BHU'), (4, 'Afghanistan', 'AFG'), (5, 'Pakistan', 'PAK')]

In [35]:

    

cur.execute('SELECT * FROM countries WHERE name LIKE "%an%"')
cur.fetchall()
#  last ma ra 1st ma  j bhaye ni huncha


    

    
Out[35]:





[(3, 'Bhutan', 'BHU'), (4, 'Afghanistan', 'AFG'), (5, 'Pakistan', 'PAK')]

In [36]:

    

cur.execute('SELECT * FROM countries WHERE name LIKE "an%"')
cur.fetchall()
# must start with an


    

    
Out[36]:





[]

In [48]:

    

cur.execute('DELETE FROM countries')
cur.fetchall()


    

    
Out[48]:





[]

In [ ]:

    




    

In [59]:

    

import csv


    

In [51]:

    

sql='INSERT INTO  countries (id,name ,iso3) VALUES (?,?,?)'
_id= 1
with open('netdata.txt','r') as datafile:
    csvfile=csv.DictReader(datafile)
    for row in csvfile:
        if row['Common Name'] and row['ISO 3166-1 3 Letter Code']:
            cur.execute(sql, (_id, row['Common Name'], row['ISO 3166-1 3 Letter Code']))
            _id+=1
conn.commit()


    

In [ ]:

    




    

In [52]:

    

cur.execute('SELECT * FROM countries')
cur.fetchall()


    

    
Out[52]:





[(1, 'Afghanistan', 'AFG'),
 (2, 'Albania', 'ALB'),
 (3, 'Algeria', 'DZA'),
 (4, 'Andorra', 'AND'),
 (5, 'Angola', 'AGO'),
 (6, 'Antigua and Barbuda', 'ATG'),
 (7, 'Argentina', 'ARG'),
 (8, 'Armenia', 'ARM'),
 (9, 'Australia', 'AUS'),
 (10, 'Austria', 'AUT'),
 (11, 'Azerbaijan', 'AZE'),
 (12, 'Bahamas, The', 'BHS'),
 (13, 'Bahrain', 'BHR'),
 (14, 'Bangladesh', 'BGD'),
 (15, 'Barbados', 'BRB'),
 (16, 'Belarus', 'BLR'),
 (17, 'Belgium', 'BEL'),
 (18, 'Belize', 'BLZ'),
 (19, 'Benin', 'BEN'),
 (20, 'Bhutan', 'BTN'),
 (21, 'Bolivia', 'BOL'),
 (22, 'Bosnia and Herzegovina', 'BIH'),
 (23, 'Botswana', 'BWA'),
 (24, 'Brazil', 'BRA'),
 (25, 'Brunei', 'BRN'),
 (26, 'Bulgaria', 'BGR'),
 (27, 'Burkina Faso', 'BFA'),
 (28, 'Burundi', 'BDI'),
 (29, 'Cambodia', 'KHM'),
 (30, 'Cameroon', 'CMR'),
 (31, 'Canada', 'CAN'),
 (32, 'Cape Verde', 'CPV'),
 (33, 'Central African Republic', 'CAF'),
 (34, 'Chad', 'TCD'),
 (35, 'Chile', 'CHL'),
 (36, "China, People's Republic of", 'CHN'),
 (37, 'Colombia', 'COL'),
 (38, 'Comoros', 'COM'),
 (39, 'Congo, (Congo – Kinshasa)', 'COD'),
 (40, 'Congo, (Congo – Brazzaville)', 'COG'),
 (41, 'Costa Rica', 'CRI'),
 (42, "Cote d'Ivoire (Ivory Coast)", 'CIV'),
 (43, 'Croatia', 'HRV'),
 (44, 'Cuba', 'CUB'),
 (45, 'Cyprus', 'CYP'),
 (46, 'Czech Republic', 'CZE'),
 (47, 'Denmark', 'DNK'),
 (48, 'Djibouti', 'DJI'),
 (49, 'Dominica', 'DMA'),
 (50, 'Dominican Republic', 'DOM'),
 (51, 'Ecuador', 'ECU'),
 (52, 'Egypt', 'EGY'),
 (53, 'El Salvador', 'SLV'),
 (54, 'Equatorial Guinea', 'GNQ'),
 (55, 'Eritrea', 'ERI'),
 (56, 'Estonia', 'EST'),
 (57, 'Ethiopia', 'ETH'),
 (58, 'Fiji', 'FJI'),
 (59, 'Finland', 'FIN'),
 (60, 'France', 'FRA'),
 (61, 'Gabon', 'GAB'),
 (62, 'Gambia, The', 'GMB'),
 (63, 'Georgia', 'GEO'),
 (64, 'Germany', 'DEU'),
 (65, 'Ghana', 'GHA'),
 (66, 'Greece', 'GRC'),
 (67, 'Grenada', 'GRD'),
 (68, 'Guatemala', 'GTM'),
 (69, 'Guinea', 'GIN'),
 (70, 'Guinea-Bissau', 'GNB'),
 (71, 'Guyana', 'GUY'),
 (72, 'Haiti', 'HTI'),
 (73, 'Honduras', 'HND'),
 (74, 'Hungary', 'HUN'),
 (75, 'Iceland', 'ISL'),
 (76, 'India', 'IND'),
 (77, 'Indonesia', 'IDN'),
 (78, 'Iran', 'IRN'),
 (79, 'Iraq', 'IRQ'),
 (80, 'Ireland', 'IRL'),
 (81, 'Israel', 'ISR'),
 (82, 'Italy', 'ITA'),
 (83, 'Jamaica', 'JAM'),
 (84, 'Japan', 'JPN'),
 (85, 'Jordan', 'JOR'),
 (86, 'Kazakhstan', 'KAZ'),
 (87, 'Kenya', 'KEN'),
 (88, 'Kiribati', 'KIR'),
 (89, 'Korea, North', 'PRK'),
 (90, 'Korea, South', 'KOR'),
 (91, 'Kuwait', 'KWT'),
 (92, 'Kyrgyzstan', 'KGZ'),
 (93, 'Laos', 'LAO'),
 (94, 'Latvia', 'LVA'),
 (95, 'Lebanon', 'LBN'),
 (96, 'Lesotho', 'LSO'),
 (97, 'Liberia', 'LBR'),
 (98, 'Libya', 'LBY'),
 (99, 'Liechtenstein', 'LIE'),
 (100, 'Lithuania', 'LTU'),
 (101, 'Luxembourg', 'LUX'),
 (102, 'Macedonia', 'MKD'),
 (103, 'Madagascar', 'MDG'),
 (104, 'Malawi', 'MWI'),
 (105, 'Malaysia', 'MYS'),
 (106, 'Maldives', 'MDV'),
 (107, 'Mali', 'MLI'),
 (108, 'Malta', 'MLT'),
 (109, 'Marshall Islands', 'MHL'),
 (110, 'Mauritania', 'MRT'),
 (111, 'Mauritius', 'MUS'),
 (112, 'Mexico', 'MEX'),
 (113, 'Micronesia', 'FSM'),
 (114, 'Moldova', 'MDA'),
 (115, 'Monaco', 'MCO'),
 (116, 'Mongolia', 'MNG'),
 (117, 'Montenegro', 'MNE'),
 (118, 'Morocco', 'MAR'),
 (119, 'Mozambique', 'MOZ'),
 (120, 'Myanmar (Burma)', 'MMR'),
 (121, 'Namibia', 'NAM'),
 (122, 'Nauru', 'NRU'),
 (123, 'Nepal', 'NPL'),
 (124, 'Netherlands', 'NLD'),
 (125, 'New Zealand', 'NZL'),
 (126, 'Nicaragua', 'NIC'),
 (127, 'Niger', 'NER'),
 (128, 'Nigeria', 'NGA'),
 (129, 'Norway', 'NOR'),
 (130, 'Oman', 'OMN'),
 (131, 'Pakistan', 'PAK'),
 (132, 'Palau', 'PLW'),
 (133, 'Panama', 'PAN'),
 (134, 'Papua New Guinea', 'PNG'),
 (135, 'Paraguay', 'PRY'),
 (136, 'Peru', 'PER'),
 (137, 'Philippines', 'PHL'),
 (138, 'Poland', 'POL'),
 (139, 'Portugal', 'PRT'),
 (140, 'Qatar', 'QAT'),
 (141, 'Romania', 'ROU'),
 (142, 'Russia', 'RUS'),
 (143, 'Rwanda', 'RWA'),
 (144, 'Saint Kitts and Nevis', 'KNA'),
 (145, 'Saint Lucia', 'LCA'),
 (146, 'Saint Vincent and the Grenadines', 'VCT'),
 (147, 'Samoa', 'WSM'),
 (148, 'San Marino', 'SMR'),
 (149, 'Sao Tome and Principe', 'STP'),
 (150, 'Saudi Arabia', 'SAU'),
 (151, 'Senegal', 'SEN'),
 (152, 'Serbia', 'SRB'),
 (153, 'Seychelles', 'SYC'),
 (154, 'Sierra Leone', 'SLE'),
 (155, 'Singapore', 'SGP'),
 (156, 'Slovakia', 'SVK'),
 (157, 'Slovenia', 'SVN'),
 (158, 'Solomon Islands', 'SLB'),
 (159, 'Somalia', 'SOM'),
 (160, 'South Africa', 'ZAF'),
 (161, 'Spain', 'ESP'),
 (162, 'Sri Lanka', 'LKA'),
 (163, 'Sudan', 'SDN'),
 (164, 'Suriname', 'SUR'),
 (165, 'Swaziland', 'SWZ'),
 (166, 'Sweden', 'SWE'),
 (167, 'Switzerland', 'CHE'),
 (168, 'Syria', 'SYR'),
 (169, 'Tajikistan', 'TJK'),
 (170, 'Tanzania', 'TZA'),
 (171, 'Thailand', 'THA'),
 (172, 'Timor-Leste (East Timor)', 'TLS'),
 (173, 'Togo', 'TGO'),
 (174, 'Tonga', 'TON'),
 (175, 'Trinidad and Tobago', 'TTO'),
 (176, 'Tunisia', 'TUN'),
 (177, 'Turkey', 'TUR'),
 (178, 'Turkmenistan', 'TKM'),
 (179, 'Tuvalu', 'TUV'),
 (180, 'Uganda', 'UGA'),
 (181, 'Ukraine', 'UKR'),
 (182, 'United Arab Emirates', 'ARE'),
 (183, 'United Kingdom', 'GBR'),
 (184, 'United States', 'USA'),
 (185, 'Uruguay', 'URY'),
 (186, 'Uzbekistan', 'UZB'),
 (187, 'Vanuatu', 'VUT'),
 (188, 'Vatican City', 'VAT'),
 (189, 'Venezuela', 'VEN'),
 (190, 'Vietnam', 'VNM'),
 (191, 'Yemen', 'YEM'),
 (192, 'Zambia', 'ZMB'),
 (193, 'Zimbabwe', 'ZWE'),
 (194, 'Abkhazia', 'GEO'),
 (195, 'China, Republic of (Taiwan)', 'TWN'),
 (196, 'Nagorno-Karabakh', 'AZE'),
 (197, 'Northern Cyprus', 'CYP'),
 (198, 'Pridnestrovie (Transnistria)', 'MDA'),
 (199, 'Somaliland', 'SOM'),
 (200, 'South Ossetia', 'GEO'),
 (201, 'Ashmore and Cartier Islands', 'AUS'),
 (202, 'Christmas Island', 'CXR'),
 (203, 'Cocos (Keeling) Islands', 'CCK'),
 (204, 'Coral Sea Islands', 'AUS'),
 (205, 'Heard Island and McDonald Islands', 'HMD'),
 (206, 'Norfolk Island', 'NFK'),
 (207, 'New Caledonia', 'NCL'),
 (208, 'French Polynesia', 'PYF'),
 (209, 'Mayotte', 'MYT'),
 (210, 'Saint Barthelemy', 'GLP'),
 (211, 'Saint Martin', 'GLP'),
 (212, 'Saint Pierre and Miquelon', 'SPM'),
 (213, 'Wallis and Futuna', 'WLF'),
 (214, 'French Southern and Antarctic Lands', 'ATF'),
 (215, 'Clipperton Island', 'PYF'),
 (216, 'Bouvet Island', 'BVT'),
 (217, 'Cook Islands', 'COK'),
 (218, 'Niue', 'NIU'),
 (219, 'Tokelau', 'TKL'),
 (220, 'Guernsey', 'GGY'),
 (221, 'Isle of Man', 'IMN'),
 (222, 'Jersey', 'JEY'),
 (223, 'Anguilla', 'AIA'),
 (224, 'Bermuda', 'BMU'),
 (225, 'British Indian Ocean Territory', 'IOT'),
 (226, 'British Virgin Islands', 'VGB'),
 (227, 'Cayman Islands', 'CYM'),
 (228, 'Falkland Islands (Islas Malvinas)', 'FLK'),
 (229, 'Gibraltar', 'GIB'),
 (230, 'Montserrat', 'MSR'),
 (231, 'Pitcairn Islands', 'PCN'),
 (232, 'Saint Helena', 'SHN'),
 (233, 'South Georgia & South Sandwich Islands', 'SGS'),
 (234, 'Turks and Caicos Islands', 'TCA'),
 (235, 'Northern Mariana Islands', 'MNP'),
 (236, 'Puerto Rico', 'PRI'),
 (237, 'American Samoa', 'ASM'),
 (238, 'Baker Island', 'UMI'),
 (239, 'Guam', 'GUM'),
 (240, 'Howland Island', 'UMI'),
 (241, 'Jarvis Island', 'UMI'),
 (242, 'Johnston Atoll', 'UMI'),
 (243, 'Kingman Reef', 'UMI'),
 (244, 'Midway Islands', 'UMI'),
 (245, 'Navassa Island', 'UMI'),
 (246, 'Palmyra Atoll', 'UMI'),
 (247, 'U.S. Virgin Islands', 'VIR'),
 (248, 'Wake Island', 'UMI'),
 (249, 'Hong Kong', 'HKG'),
 (250, 'Macau', 'MAC'),
 (251, 'Faroe Islands', 'FRO'),
 (252, 'Greenland', 'GRL'),
 (253, 'French Guiana', 'GUF'),
 (254, 'Guadeloupe', 'GLP'),
 (255, 'Martinique', 'MTQ'),
 (256, 'Reunion', 'REU'),
 (257, 'Aland', 'ALA'),
 (258, 'Aruba', 'ABW'),
 (259, 'Netherlands Antilles', 'ANT'),
 (260, 'Svalbard', 'SJM'),
 (261, 'Ascension', 'ASC'),
 (262, 'Tristan da Cunha', 'TAA'),
 (263, 'Australian Antarctic Territory', 'ATA'),
 (264, 'Ross Dependency', 'ATA'),
 (265, 'Peter I Island', 'ATA'),
 (266, 'Queen Maud Land', 'ATA'),
 (267, 'British Antarctic Territory', 'ATA')]

In [70]:

    

cur.execute('DELETE FROM country_list')
cur.fetchall()


    

    
Out[70]:





[]

In [71]:

    

sql= '''CREATE TABLE
country_list (id integer primary key autoincrement,
country_name text not null,
iso3 text not null unique)'''
cur.execute(sql)


    

    




---------------------------------------------------------------------------
OperationalError                          Traceback (most recent call last)
<ipython-input-71-24022c1bbd09> in <module>()
      3 country_name text not null,
      4 iso3 text not null unique)'''
----> 5 cur.execute(sql)

OperationalError: table country_list already exists

In [72]:

    

sql='INSERT INTO  country_list (country_name ,iso3) VALUES (?,?)'
with open('netdata.txt','r') as datafile:
    csvfile=csv.DictReader(datafile)
    for row in csvfile:
        if row['Common Name'] and row['Formal Name']:
            cur.execute(sql, (row['Common Name'], row['Formal Name']))
conn.commit()


    

In [73]:

    

cur.execute('SELECT * FROM country_list')
cur.fetchall()


    

    
Out[73]:





[(395, 'Afghanistan', 'Islamic State of Afghanistan'),
 (396, 'Albania', 'Republic of Albania'),
 (397, 'Algeria', "People's Democratic Republic of Algeria"),
 (398, 'Andorra', 'Principality of Andorra'),
 (399, 'Angola', 'Republic of Angola'),
 (400, 'Argentina', 'Argentine Republic'),
 (401, 'Armenia', 'Republic of Armenia'),
 (402, 'Australia', 'Commonwealth of Australia'),
 (403, 'Austria', 'Republic of Austria'),
 (404, 'Azerbaijan', 'Republic of Azerbaijan'),
 (405, 'Bahamas, The', 'Commonwealth of The Bahamas'),
 (406, 'Bahrain', 'Kingdom of Bahrain'),
 (407, 'Bangladesh', "People's Republic of Bangladesh"),
 (408, 'Belarus', 'Republic of Belarus'),
 (409, 'Belgium', 'Kingdom of Belgium'),
 (410, 'Benin', 'Republic of Benin'),
 (411, 'Bhutan', 'Kingdom of Bhutan'),
 (412, 'Bolivia', 'Republic of Bolivia'),
 (413, 'Botswana', 'Republic of Botswana'),
 (414, 'Brazil', 'Federative Republic of Brazil'),
 (415, 'Brunei', 'Negara Brunei Darussalam'),
 (416, 'Bulgaria', 'Republic of Bulgaria'),
 (417, 'Burundi', 'Republic of Burundi'),
 (418, 'Cambodia', 'Kingdom of Cambodia'),
 (419, 'Cameroon', 'Republic of Cameroon'),
 (420, 'Cape Verde', 'Republic of Cape Verde'),
 (421, 'Chad', 'Republic of Chad'),
 (422, 'Chile', 'Republic of Chile'),
 (423, "China, People's Republic of", "People's Republic of China"),
 (424, 'Colombia', 'Republic of Colombia'),
 (425, 'Comoros', 'Union of Comoros'),
 (426, 'Congo, (Congo – Kinshasa)', 'Democratic Republic of the Congo'),
 (427, 'Congo, (Congo – Brazzaville)', 'Republic of the Congo'),
 (428, 'Costa Rica', 'Republic of Costa Rica'),
 (429, "Cote d'Ivoire (Ivory Coast)", "Republic of Cote d'Ivoire"),
 (430, 'Croatia', 'Republic of Croatia'),
 (431, 'Cuba', 'Republic of Cuba'),
 (432, 'Cyprus', 'Republic of Cyprus'),
 (433, 'Denmark', 'Kingdom of Denmark'),
 (434, 'Djibouti', 'Republic of Djibouti'),
 (435, 'Dominica', 'Commonwealth of Dominica'),
 (436, 'Ecuador', 'Republic of Ecuador'),
 (437, 'Egypt', 'Arab Republic of Egypt'),
 (438, 'El Salvador', 'Republic of El Salvador'),
 (439, 'Equatorial Guinea', 'Republic of Equatorial Guinea'),
 (440, 'Eritrea', 'State of Eritrea'),
 (441, 'Estonia', 'Republic of Estonia'),
 (442, 'Ethiopia', 'Federal Democratic Republic of Ethiopia'),
 (443, 'Fiji', 'Republic of the Fiji Islands'),
 (444, 'Finland', 'Republic of Finland'),
 (445, 'France', 'French Republic'),
 (446, 'Gabon', 'Gabonese Republic'),
 (447, 'Gambia, The', 'Republic of The Gambia'),
 (448, 'Georgia', 'Republic of Georgia'),
 (449, 'Germany', 'Federal Republic of Germany'),
 (450, 'Ghana', 'Republic of Ghana'),
 (451, 'Greece', 'Hellenic Republic'),
 (452, 'Guatemala', 'Republic of Guatemala'),
 (453, 'Guinea', 'Republic of Guinea'),
 (454, 'Guinea-Bissau', 'Republic of Guinea-Bissau'),
 (455, 'Guyana', 'Co-operative Republic of Guyana'),
 (456, 'Haiti', 'Republic of Haiti'),
 (457, 'Honduras', 'Republic of Honduras'),
 (458, 'Hungary', 'Republic of Hungary'),
 (459, 'Iceland', 'Republic of Iceland'),
 (460, 'India', 'Republic of India'),
 (461, 'Indonesia', 'Republic of Indonesia'),
 (462, 'Iran', 'Islamic Republic of Iran'),
 (463, 'Iraq', 'Republic of Iraq'),
 (464, 'Israel', 'State of Israel'),
 (465, 'Italy', 'Italian Republic'),
 (466, 'Jordan', 'Hashemite Kingdom of Jordan'),
 (467, 'Kazakhstan', 'Republic of Kazakhstan'),
 (468, 'Kenya', 'Republic of Kenya'),
 (469, 'Kiribati', 'Republic of Kiribati'),
 (470, 'Korea, North', "Democratic People's Republic of Korea"),
 (471, 'Korea, South', 'Republic of Korea'),
 (472, 'Kuwait', 'State of Kuwait'),
 (473, 'Kyrgyzstan', 'Kyrgyz Republic'),
 (474, 'Laos', "Lao People's Democratic Republic"),
 (475, 'Latvia', 'Republic of Latvia'),
 (476, 'Lebanon', 'Lebanese Republic'),
 (477, 'Lesotho', 'Kingdom of Lesotho'),
 (478, 'Liberia', 'Republic of Liberia'),
 (479, 'Libya', "Great Socialist People's Libyan Arab Jamahiriya"),
 (480, 'Liechtenstein', 'Principality of Liechtenstein'),
 (481, 'Lithuania', 'Republic of Lithuania'),
 (482, 'Luxembourg', 'Grand Duchy of Luxembourg'),
 (483, 'Macedonia', 'Republic of Macedonia'),
 (484, 'Madagascar', 'Republic of Madagascar'),
 (485, 'Malawi', 'Republic of Malawi'),
 (486, 'Maldives', 'Republic of Maldives'),
 (487, 'Mali', 'Republic of Mali'),
 (488, 'Malta', 'Republic of Malta'),
 (489, 'Marshall Islands', 'Republic of the Marshall Islands'),
 (490, 'Mauritania', 'Islamic Republic of Mauritania'),
 (491, 'Mauritius', 'Republic of Mauritius'),
 (492, 'Mexico', 'United Mexican States'),
 (493, 'Micronesia', 'Federated States of Micronesia'),
 (494, 'Moldova', 'Republic of Moldova'),
 (495, 'Monaco', 'Principality of Monaco'),
 (496, 'Montenegro', 'Republic of Montenegro'),
 (497, 'Morocco', 'Kingdom of Morocco'),
 (498, 'Mozambique', 'Republic of Mozambique'),
 (499, 'Myanmar (Burma)', 'Union of Myanmar'),
 (500, 'Namibia', 'Republic of Namibia'),
 (501, 'Nauru', 'Republic of Nauru'),
 (502, 'Netherlands', 'Kingdom of the Netherlands'),
 (503, 'Nicaragua', 'Republic of Nicaragua'),
 (504, 'Niger', 'Republic of Niger'),
 (505, 'Nigeria', 'Federal Republic of Nigeria'),
 (506, 'Norway', 'Kingdom of Norway'),
 (507, 'Oman', 'Sultanate of Oman'),
 (508, 'Pakistan', 'Islamic Republic of Pakistan'),
 (509, 'Palau', 'Republic of Palau'),
 (510, 'Panama', 'Republic of Panama'),
 (511, 'Papua New Guinea', 'Independent State of Papua New Guinea'),
 (512, 'Paraguay', 'Republic of Paraguay'),
 (513, 'Peru', 'Republic of Peru'),
 (514, 'Philippines', 'Republic of the Philippines'),
 (515, 'Poland', 'Republic of Poland'),
 (516, 'Portugal', 'Portuguese Republic'),
 (517, 'Qatar', 'State of Qatar'),
 (518, 'Russia', 'Russian Federation'),
 (519, 'Rwanda', 'Republic of Rwanda'),
 (520, 'Saint Kitts and Nevis', 'Federation of Saint Kitts and Nevis'),
 (521, 'Samoa', 'Independent State of Samoa'),
 (522, 'San Marino', 'Republic of San Marino'),
 (523,
  'Sao Tome and Principe',
  'Democratic Republic of Sao Tome and Principe'),
 (524, 'Saudi Arabia', 'Kingdom of Saudi Arabia'),
 (525, 'Senegal', 'Republic of Senegal'),
 (526, 'Serbia', 'Republic of Serbia'),
 (527, 'Seychelles', 'Republic of Seychelles'),
 (528, 'Sierra Leone', 'Republic of Sierra Leone'),
 (529, 'Singapore', 'Republic of Singapore'),
 (530, 'Slovakia', 'Slovak Republic'),
 (531, 'Slovenia', 'Republic of Slovenia'),
 (532, 'South Africa', 'Republic of South Africa'),
 (533, 'Spain', 'Kingdom of Spain'),
 (534, 'Sri Lanka', 'Democratic Socialist Republic of Sri Lanka'),
 (535, 'Sudan', 'Republic of the Sudan'),
 (536, 'Suriname', 'Republic of Suriname'),
 (537, 'Swaziland', 'Kingdom of Swaziland'),
 (538, 'Sweden', 'Kingdom of Sweden'),
 (539, 'Switzerland', 'Swiss Confederation'),
 (540, 'Syria', 'Syrian Arab Republic'),
 (541, 'Tajikistan', 'Republic of Tajikistan'),
 (542, 'Tanzania', 'United Republic of Tanzania'),
 (543, 'Thailand', 'Kingdom of Thailand'),
 (544, 'Timor-Leste (East Timor)', 'Democratic Republic of Timor-Leste'),
 (545, 'Togo', 'Togolese Republic'),
 (546, 'Tonga', 'Kingdom of Tonga'),
 (547, 'Trinidad and Tobago', 'Republic of Trinidad and Tobago'),
 (548, 'Tunisia', 'Tunisian Republic'),
 (549, 'Turkey', 'Republic of Turkey'),
 (550, 'Uganda', 'Republic of Uganda'),
 (551, 'United Arab Emirates', 'United Arab Emirates'),
 (552,
  'United Kingdom',
  'United Kingdom of Great Britain and Northern Ireland'),
 (553, 'United States', 'United States of America'),
 (554, 'Uruguay', 'Oriental Republic of Uruguay'),
 (555, 'Uzbekistan', 'Republic of Uzbekistan'),
 (556, 'Vanuatu', 'Republic of Vanuatu'),
 (557, 'Vatican City', 'State of the Vatican City'),
 (558, 'Venezuela', 'Bolivarian Republic of Venezuela'),
 (559, 'Vietnam', 'Socialist Republic of Vietnam'),
 (560, 'Yemen', 'Republic of Yemen'),
 (561, 'Zambia', 'Republic of Zambia'),
 (562, 'Zimbabwe', 'Republic of Zimbabwe'),
 (563, 'Abkhazia', 'Republic of Abkhazia'),
 (564, 'China, Republic of (Taiwan)', 'Republic of China'),
 (565, 'Nagorno-Karabakh', 'Nagorno-Karabakh Republic'),
 (566, 'Northern Cyprus', 'Turkish Republic of Northern Cyprus'),
 (567, 'Pridnestrovie (Transnistria)', 'Pridnestrovian Moldavian Republic'),
 (568, 'Somaliland', 'Republic of Somaliland'),
 (569, 'South Ossetia', 'Republic of South Ossetia'),
 (570,
  'Ashmore and Cartier Islands',
  'Territory of Ashmore and Cartier Islands'),
 (571, 'Christmas Island', 'Territory of Christmas Island'),
 (572, 'Cocos (Keeling) Islands', 'Territory of Cocos (Keeling) Islands'),
 (573, 'Coral Sea Islands', 'Coral Sea Islands Territory'),
 (574,
  'Heard Island and McDonald Islands',
  'Territory of Heard Island and McDonald Islands'),
 (575, 'Norfolk Island', 'Territory of Norfolk Island'),
 (576, 'French Polynesia', 'Overseas Country of French Polynesia'),
 (577, 'Mayotte', 'Departmental Collectivity of Mayotte'),
 (578, 'Saint Barthelemy', 'Collectivity of Saint Barthelemy'),
 (579, 'Saint Martin', 'Collectivity of Saint Martin'),
 (580,
  'Saint Pierre and Miquelon',
  'Territorial Collectivity of Saint Pierre and Miquelon'),
 (581, 'Wallis and Futuna', 'Collectivity of the Wallis and Futuna Islands'),
 (582,
  'French Southern and Antarctic Lands',
  'Territory of the French Southern and Antarctic Lands'),
 (583, 'Guernsey', 'Bailiwick of Guernsey'),
 (584, 'Jersey', 'Bailiwick of Jersey'),
 (585,
  'Northern Mariana Islands',
  'Commonwealth of The Northern Mariana Islands'),
 (586, 'Puerto Rico', 'Commonwealth of Puerto Rico'),
 (587, 'American Samoa', 'Territory of American Samoa'),
 (588, 'Guam', 'Territory of Guam'),
 (589, 'U.S. Virgin Islands', 'United States Virgin Islands'),
 (590, 'Hong Kong', 'Hong Kong Special Administrative Region'),
 (591, 'Macau', 'Macau Special Administrative Region'),
 (592, 'French Guiana', 'Overseas Region of Guiana'),
 (593, 'Guadeloupe', 'Overseas Region of Guadeloupe'),
 (594, 'Martinique', 'Overseas Region of Martinique'),
 (595, 'Reunion', 'Overseas Region of Reunion')]

In [74]:

    

sql='''INSERT INTO country_list (id,country_name,iso3) VALUES (595, 'Reunion', 'Overseas Region of Reunion')'''
cur.execute(sql)


    

    




---------------------------------------------------------------------------
IntegrityError                            Traceback (most recent call last)
<ipython-input-74-a2af9509e36d> in <module>()
      1 sql='''INSERT INTO country_list (id,country_name,iso3) VALUES (595, 'Reunion', 'Overseas Region of Reunion')'''
----> 2 cur.execute(sql)

IntegrityError: UNIQUE constraint failed: country_list.id

In [ ]:

    




    

In [ ]:

    




    

In [ ]:

    




    

In [ ]:

    




    

In [ ]:

    




    

In [ ]:

    




    

In [ ]:

    




    

In [ ]:

    




    

In [ ]:

    




    

In [ ]:

    




    

In [ ]:

    




    

In [ ]: